package org.ccccye.multilevel.service;

import com.google.common.collect.Lists;
import org.ccccye.multilevel.pojo.Category;
import org.ccccye.multilevel.pojo.Category1;
import org.ccccye.multilevel.pojo.CategoryTreeNode1;
import org.ccccye.multilevel.pojo.TreeNode;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.lang.NonNull;
import org.springframework.stereotype.Service;
import org.springframework.util.Assert;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

/**
 * 无限级分类
 * 方案一： 父类id法
 */
@Service
public class MultiLevelService1 implements MultiLevelService{
    private JdbcTemplate jdbcTemplate;

    @Autowired
    public MultiLevelService1(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 查询节点和它的子节点
     * @param id 0即为查询全部节点
     * @return
     */
    @Override
    public List selectList(Integer id) {
        Assert.notNull(id, "id不能为空");

        String sql = "select prod_id as prodId, prod_name as prodName, pid from prod_category_1 where prod_id = ?";
        RowMapper<Category1> rowMapper = new BeanPropertyRowMapper(Category1.class);
        List<Category1> tmpList = jdbcTemplate.query(sql, rowMapper, id);
        Category1 category = null;
        if (tmpList.size() != 0){
            category = tmpList.get(0);
        }

        sql = "select prod_id as prodId, prod_name as prodName, pid from prod_category_1";
        List<Category1> list = jdbcTemplate.query(sql, rowMapper);

        List<Category1> ret = new ArrayList<>();
        if (category != null){
            ret.add(category);
        }
        findChild(list, ret, id);

        return ret;
    }

    /**
     * 查询节点和它的子节点（树）
     * @param id 0即为查询全部节点
     * @return
     */
    @Override
    public List selectListForTree(Integer id) {
        Assert.notNull(id, "id不能为空");

        String sql = "select prod_id as prodId, prod_name as prodName, pid from prod_category_1 where prod_id = ?";
        RowMapper<Category1> rowMapper = new BeanPropertyRowMapper(Category1.class);
        List<Category1> tmpList = jdbcTemplate.query(sql, rowMapper, id);
        Category1 category = null;
        CategoryTreeNode1 categoryTreeNode1 = null;
        if (tmpList.size() != 0){
            category = tmpList.get(0);
            try {
                categoryTreeNode1 = CategoryTreeNode1.class.newInstance();
                BeanUtils.copyProperties(category, categoryTreeNode1);
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }

        sql = "select prod_id as prodId, prod_name as prodName, pid from prod_category_1";
        List<Category1> list = jdbcTemplate.query(sql, rowMapper);

        List<CategoryTreeNode1> treeNodeList = list.stream().map(category1 -> {
            try {
                CategoryTreeNode1 tmp = CategoryTreeNode1.class.newInstance();
                BeanUtils.copyProperties(category1, tmp);
                return tmp;
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            return null;
        }).collect(Collectors.toList());

        List<CategoryTreeNode1> retTreeList = buildTree(treeNodeList, id);

        if (categoryTreeNode1 != null){
            categoryTreeNode1.setChildList(retTreeList);
            return Lists.newArrayList(categoryTreeNode1);
        }

        return retTreeList;
    }

    /**
     * 查询同层级的节点
     * @param id
     * @return
     */
    @Override
    public List selectPeerById(Integer id) {
        Assert.notNull(id, "id不能为空");

        String sql = "select c1.prod_id as prodId, c1.prod_name as prodName, c1.pid from prod_category_1 c1 join prod_category_1 c2 on c1.pid = c2.pid where c2.prod_id = ?";
        RowMapper<Category1> rowMapper = new BeanPropertyRowMapper<>(Category1.class);
        return jdbcTemplate.query(sql, rowMapper, id);
    }

    /**
     * 查询节点的直属子节点
     * @param id
     * @return
     */
    @Override
    public List selectChildById(Integer id) {
        Assert.notNull(id, "id不能为空");

        String sql = "select c1.prod_id as prodId, c1.prod_name as prodName, c1.pid from prod_category_1 c1 join prod_category_1 c2 on c1.prod_id = c2.pid where c2.prod_id = ?";
        RowMapper<Category1> rowMapper = new BeanPropertyRowMapper<>(Category1.class);
        return jdbcTemplate.query(sql, rowMapper, id);
    }

    /**
     * 查询节点的深度
     * @param id
     * @return
     */
    @Override
    public Integer deepById(Integer id) {
        Assert.notNull(id, "id不能为空");

        String sql = "select prod_id as prodId, prod_name as prodName, pid from prod_category_1";
        List<Category1> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Category1.class));
        Integer level = 0;
        level = findDeep(list, id, level);
        return level;
    }

    /**
     * 插入节点
     * @param category
     * @return
     */
    @Override
    public Integer insertNode(Category category) {
        Assert.notNull(category, "category不允许为空");
        if (!(category instanceof Category1)){
            return 0;
        }
        Category1 cg = (Category1) category;

        Assert.notNull(cg.getProdId(), "id不允许为空");
        Assert.notNull(cg.getPid(), "pid不允许为空");

        String sql = "insert into prod_category_1(prod_id, prod_name, pid) values(?, ?, ?)";

        return jdbcTemplate.update(sql, cg.getProdId(), cg.getProdName(), cg.getPid());
    }

    /**
     * 更新节点
     * @param category
     * @return
     */
    @Override
    public Integer updateNode(Category category) {
        Assert.notNull(category, "category不允许为空");
        if (!(category instanceof Category1)){
            return 0;
        }
        Category1 cg = (Category1) category;
        Assert.notNull(cg.getProdId(), "id不允许为空");
        Assert.notNull(cg.getPid(), "pid不允许为空");

        String sql = "UPDATE prod_category_1 SET prod_name = ?, pid = ? WHERE prod_id = ?";

        return jdbcTemplate.update(sql, cg.getProdName(), cg.getPid(), cg.getProdId());
    }

    /**
     * 删除节点
     * @param category
     * @return
     */
    @Override
    public Integer deleteNode(Category category) {
        Assert.notNull(category, "category不允许为空");
        if (!(category instanceof Category1)){
            return 0;
        }
        Category1 cg = (Category1) category;
        Assert.notNull(cg.getProdId(), "id不允许为空");

        List<Category1> list = selectList(cg.getProdId());

        String sql =  "DELETE FROM prod_category_1 WHERE prod_id = ?";
        int[] retList = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                Category1 category = list.get(i);
                preparedStatement.setInt(1, category.getProdId());
            }

            @Override
            public int getBatchSize() {
                return list.size();
            }
        });

        Integer ret = 0;
        for (int r : retList){
            if (r > 0){
                ret = ret+r;
            }
        }

        return ret;
    }

    /**
     * 查找结点深度-递归
     * 可以用递归优化，减少StackOverflow可能性
     * @param list
     * @param id
     * @param level 层级从1开始
     * @return
     */
    private Integer findDeep(List<Category1> list, Integer id, Integer level){
        for (Category1 category : list){
            Integer prodId = category.getProdId();
            Integer pid = category.getPid();
            if (id.intValue() == prodId.intValue()){
                if (pid != null){
                    level = level == 0 ? 1 : ++level;
                    level = findDeep(list, pid, level);
                }
            }
        }
        return level;
    }

    /**
     * 查找子节点-递归
     * 可以用递归优化，减少StackOverflow可能性
     * @param list
     * @param parentId
     * @return
     */
    private void findChild(List<Category1> list, @NonNull List<Category1> retList, Integer parentId){
        for (Category1 category : list){
            Integer prodId = category.getProdId();
            Integer pid = category.getPid();
            if (parentId.intValue() == pid.intValue()){
                retList.add(category);
                findChild(list, retList, prodId);
            }
        }
    }

    /**
     * 构建树结构
     * @param list
     * @param parentId
     * @return
     */
    private List<CategoryTreeNode1> buildTree(List<CategoryTreeNode1> list, Integer parentId){
        List<CategoryTreeNode1> result = new ArrayList<>();
        for (CategoryTreeNode1 categoryTreeNode1 : list){
            Integer prodId = categoryTreeNode1.getProdId();
            Integer pid = categoryTreeNode1.getPid();
            if (parentId.intValue() == pid.intValue()){
                List<CategoryTreeNode1> childList = buildTree(list, prodId);

                categoryTreeNode1.setChildList(childList);
                result.add(categoryTreeNode1);
            }
        }
        return result;
    }
}
